{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Neeps - Easy\n",
    "\n",
    "The \"Neeps\" database includes details of all teaching events in the School of Computing at Napier University in Semester 1 of the 2000/2001 academic year.\n",
    "\n",
    "Graduated questions\n",
    "\n",
    "The timetable database contains the following tables:\n",
    "\n",
    "- ut_staff\n",
    "\n",
    "id | name\n",
    "---|-----\n",
    "co.ACg | Cumming, Andrew\n",
    "co.ACr | Crerar, Dr Alison\n",
    "co.AFA | Armitage, Dr Alistair\n",
    " | ... Result truncated.\n",
    "\n",
    "The staff table simply gives the unique identifier for each member of staff together with their full name.\n",
    "\n",
    "- ut_student\n",
    "\n",
    "id | name | sze | parent\n",
    "---|------|-----|----------\n",
    "co1.BAe | BAe | 15 | \n",
    "co1.CO | Computing 1st Year | 150 | \n",
    "co1.CO.a | Computing 1st Year a | 20 | co1.CO\n",
    "co1.CO.c | Computing 1st Year c | 20 | co1.CO\n",
    "co1.CO.d | Computing 1st Year d | 20 | co1.CO\n",
    " | | | ... Result truncated.\n",
    "\n",
    "Each entry in the student table represents a group of students who follow the same timetable. The parent field is used to maintain a hierachy of groups. A student in group co1.CO.a is also in group co1.CO\n",
    "\n",
    "- ut_room\n",
    "\n",
    "id | name | capacity | parent\n",
    "---|------|----------|---------\n",
    "co.117+118 |  | 32 | \n",
    "co.117 |  | 16 | co.117+118\n",
    "co.118 |  | 16 | co.117+118\n",
    "co.B7 |  | 40 | \n",
    " | | | ... result truncated\n",
    "\n",
    "All classrooms are listed in the room table. The parent relation is used to maintain groupings. The row co.117+118 represents a pair of rooms - sometimes these rooms are used for a single event.\n",
    "\n",
    "- ut_event\n",
    "\n",
    "id | modle | kind | dow | tod | duration | room\n",
    "---|-------|------|-----|-----|----------|----------\n",
    "co12004.L01 | co12004 | L | Wednesday | 11:00 | 1 | cr.SMH\n",
    "co12004.L02 | co12004 | L | Monday | 17:00 | 1 | cr.B13\n",
    "co12004.T01 | co12004 | T | Monday | 11:00 | 2 | co.G78+G82\n",
    "co12004.T02 | co12004 | T | Tuesday | 15:00 | 2 | co.B7\n",
    "co12004.T03 | co12004 | T | Tuesday | 13:00 | 2 | co.G78+G82\n",
    " | | | | | | ... Result truncated.\n",
    "\n",
    "Every event is associated with a single modle (deliberately mis-spelled version of module - which is a reserved word in one of engines). An event occurs on a particular day of the week (dow) at a particular time of day (tod). Every event has a duration specified as a whole number of hours.\n",
    "\n",
    "- ut_attends\n",
    "\n",
    "student | event\n",
    "--------|------\n",
    "co1.BAe | co12004.L01\n",
    "co1.CO | co12004.L01\n",
    "co1.IS | co12004.L01\n",
    "co1.SE.pt | co12004.L02\n",
    "co1.CO.a | co12004.T01\n",
    " | ... Result truncated.\n",
    "\n",
    "The attends table links event to student. It realises the many to many relation.\n",
    "\n",
    "- ut_teaches\n",
    "\n",
    "staff | event\n",
    "------|----------\n",
    "co.AMn | co12004.L01\n",
    "co.RK | co12004.L01\n",
    "co.SRM | co12004.L01\n",
    "co.RK | co12004.L02\n",
    "co.SRM | co12004.L02\n",
    " | ... Result truncated.\n",
    "\n",
    "The teaches table links event to staff.\n",
    "\n",
    "- ut_occurs\n",
    "\n",
    "event | week\n",
    "------|--------\n",
    "co12004.L01 | 01\n",
    "co12004.L01 | 02\n",
    "co12004.L01 | 03\n",
    "co12004.L01 | 04\n",
    "co12004.L01 | 05\n",
    " | ... Result truncated.\n",
    "\n",
    "Most events occur on weeks 01 to 13. This table records exactly which weeks are part of the plan for each event.\n",
    "\n",
    "- ut_modle\n",
    "\n",
    "id | name\n",
    "---|---------\n",
    "co12001 | Rapid Application Development\n",
    "co12002 | Software Development 1A\n",
    "co12003 | Professional Skills\n",
    "co12004 | Applications Workshop\n",
    " | ... Result truncated.\n",
    "\n",
    "By the 21st century, the Gradgrindians had completed their assimilation of Education. The process of \"modularisation\" was just one of their crimes against learning.\n",
    "\n",
    "- ut_week\n",
    "\n",
    "id | wkstart\n",
    "---|-------\n",
    "01 | 02/10/00\n",
    "02 | 09/10/00\n",
    "03 | 16/10/00\n",
    " | ...Result truncated\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "**Give the room id in which the event co42010.L01 takes place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "**For each event in module co72010 show the day, the time and the place.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "**List the names of the staff who teach on module co72010.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "**Give a list of the staff and module number associated with events using room cr.132 on Wednesday, include the time each event starts.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "**Give a list of the student groups which take modules with the word 'Database' in the name.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
